Horizontal partitioning is an important aspect of physical database design that has significant impact on performance. Horizontal partitioning allows access methods such as tables, indexes and materialized views to be partitioned into disjoint sets of rows that are physically stored and accessed separately. Like indexes and materialized views, horizontal partitioning can have a significant impact on the performance of the database workload (i.e., queries and updates that execute against the database system) by enabling reduced cost of accessing and processing data. Thus, database administrators (DBAs) in today's enterprises are faced with the challenging task of determining the appropriate choice of physical design consisting of partitioned tables, indexes and materialized views for optimizing the performance of the workload.
Several database tuning tools have been developed to assist the DBA in selecting indexes and materialize views. U.S. Pat. No. 6,223,171 to Chaudhuri et al. discloses a “what if” analysis tool that allows the DBA to analyze the utility of a given configuration of indexes over the database workload. U.S. Pat. No. 6,226,658 to Adya et al. discloses an index tuning wizard for selecting indexes based on a workload. U.S. Pat. No. 6,366,903 to Agrawal et al. discloses a tuning wizard that recommends an optimal combination of indexes and materialized views for a given workload. These tuning tools use a hill climbing algorithm, referred to as Greedy(m,k), as the search strategy for selecting indexes and views to be recommended. Greedy(m,k) starts by enumerating all sets of size m from the candidate set. It chooses the optimal set of size m and then greedily (based on the additional benefit accrued given the current configuration) adds the indexes until the size of the set reaches k. The value of k is determined by storage constraints.
While existing tools are useful for selecting structures in a physical design configuration for a database, none of them integrate the partitioning aspect into the selection of structures.